package com.qp.basics.estivate.jdbc;

import com.qp.basics.estivate.Query;
import com.qp.basics.estivate.ResultSetExtractor;
import com.qp.basics.estivate.Session;
import com.qp.basics.estivate.support.MapResultSetExtractor;
import com.qp.basics.estivate.support.SingleColumnResultSetExtractor;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import java.sql.*;
import java.util.Date;
import java.util.*;

public abstract class QueryJdbc implements Query {
	private static final Log logger = LogFactory.getLog(QueryJdbc.class);
	private static final boolean IS_DEBUG_ENABLED = logger.isDebugEnabled();
	// 常用的结果集抽取类。
	protected static MapResultSetExtractor mapResultSetExtractor = new MapResultSetExtractor();
	protected static SingleColumnResultSetExtractor singleColumnResultSetExtractor = new SingleColumnResultSetExtractor();

	protected Session session;
	protected String sql;
	protected Integer maxResults;
	protected Integer firstResult = 0;
	protected List<Parameter> params = new ArrayList<Parameter>();
	protected Map<String, Object> paramsMap = new HashMap<String, Object>();
	protected List<Object> generatedKeys;

	protected static class Parameter {
		public int position;
		public Object value;
	}

	public QueryJdbc(String sql) {
		this.sql = sql;
		recordLstSql(sql);
	}

	private void recordLstSql(String sql) {
		if (session == null)
			return;
		AbstractSessionImpl as = (AbstractSessionImpl) session;
		as.lstSql = sql;
	}

	protected boolean supportReturnGeneratedKey() {
		return true;
	}

	public int executeUpdate() {
		PreparedStatement pstat = null;
		generatedKeys = null;
		try {
			if (logger.isDebugEnabled())
				logger.debug(sql);
			boolean insert = sql.toLowerCase().trim().startsWith("insert");
			if (insert && supportReturnGeneratedKey()) {
				pstat = session.getConnection().prepareStatement(sql,
						Statement.RETURN_GENERATED_KEYS);
			} else
				pstat = session.getConnection().prepareStatement(sql);
			setParameters(pstat);
			int effected = pstat.executeUpdate();
			if (insert && supportReturnGeneratedKey()) {
				ResultSet keyRS = pstat.getGeneratedKeys();
				if (keyRS != null) {
					if (keyRS.next()) {
						generatedKeys = new ArrayList();
						do {
							Object key = keyRS.getObject(1);
							generatedKeys.add(key);
						} while (keyRS.next());
					}
				}
			}
			return effected;
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage() + " sq=[" + sql + "]", e);
		} finally {
			try {
				pstat.close();
			} catch (Exception e) {
			}
		}
	}

	/**
	 * 给PreparedStatement设置参数。 TODO 类型转换。。。。。。。 参数存在params里。
	 * 
	 * @param pstat
	 */
	protected void setParameters(PreparedStatement pstat) {
		int i = 0;
		// 将变量放在这里是为了获取最后一次的操作参数。
		Object v = null;
		int position = 0;
		try {
			for (Parameter p : params) {
				i++;
				if (p == null) {
					if (IS_DEBUG_ENABLED)
						logger.debug("params[" + i + "] is null.");
					continue;
				}
				v = p.value;
				position = p.position;
				if (IS_DEBUG_ENABLED) {
					logger.debug("binding '" + toString(v) + "' to parameter: "
							+ position);
				}
				if (v == null) {
					pstat.setObject(position, v);
				} else if (v instanceof Timestamp) {
					pstat.setTimestamp(position, (Timestamp) v);
				} else if (v instanceof java.sql.Date) {
					pstat.setDate(position, (java.sql.Date) v);
				} else if (v instanceof Date) {
					pstat.setTimestamp(position, new Timestamp(
							((Date) v).getTime()));
				} else if ("oracle.sql.TIMESTAMP"
						.equals(v.getClass().getName())) {
					// oracle.sql.TIMESTAMP ts = (oracle.sql.TIMESTAMP)v;
					// pstat.setTimestamp(position, new
					// java.sql.Timestamp(ts.timestampValue().getTime()));
				} else
					pstat.setObject(position, v);
			}
		} catch (SQLException e) {
			e.printStackTrace();
			throw new RuntimeException(e.getMessage() + " position:" + position
					+ " v:" + v, e);
		}
	}

	/**
	 * 从原始sql包装出一个分页的sql。 如mysql
	 * "select * from ("+sql+") limit "+this.firstResult+","+this.maxResults;
	 * 
	 * @param sql
	 * @return
	 */
	protected abstract String composePaginationSql(String sql);

	public List getResultList(ResultSetExtractor extractor) {
		ResultSet rs = null;
		PreparedStatement pstat = null;
		String sqlx = positionStrReplace();
		try {
			if (this.maxResults != null) {
				sqlx = composePaginationSql(sqlx);
			}
			if (logger.isDebugEnabled())
				logger.debug(sqlx);
			pstat = session.getConnection().prepareStatement(sqlx);
			setParameters(pstat);
			rs = pstat.executeQuery();
			List rsList = new ArrayList();
			while (rs.next()) {
				Object m = extractor.extractData(rs);
				rsList.add(m);
			}
			if (rsList.size() > 500) {
				logger.warn("results size of [" + sql + "] more than 500.");
			}
			return rsList;
		} catch (SQLException e) {
			logger.error(e.getMessage() + " sql=[" + sql + "] sqlx=[" + sqlx
					+ "]", e);
			throw new RuntimeException(e.getMessage(), e);
		} finally {
			if (pstat != null)
				try {
					pstat.close();
				} catch (SQLException e) {
					logger.error(e.getMessage() + sqlx, e);
				}
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error(e.getMessage() + sqlx, e);
				}
		}
	}

	protected String positionStrReplace() {
		String newSql = sql;
		if (!paramsMap.isEmpty()) {
			Set<String> keySet = paramsMap.keySet();
			for (String key : keySet) {
				Object v = paramsMap.get(key);
				// TODO 这里应该加上一些关于类型之类的判断和处理，这块可以参考ibatis。
				newSql = newSql.replaceAll("#" + key + "#", v.toString());
			}
		}
		return newSql;
	}

	public Object getSingleResult(ResultSetExtractor extractor) {
		ResultSet rs = null;
		PreparedStatement pstat = null;
		try {
			if (IS_DEBUG_ENABLED)
				logger.debug(sql);
			sql = positionStrReplace();
			pstat = session.getConnection().prepareStatement(sql);
			setParameters(pstat);
			rs = pstat.executeQuery();
			if (rs.next())
				return extractor.extractData(rs);
			return null;
		} catch (SQLException e) {
			throw new RuntimeException(e.getMessage() + " sq=[" + sql + "]", e);
		} finally {
			if (pstat != null)
				try {
					pstat.close();
				} catch (SQLException e) {
					logger.error(e.getMessage() + sql, e);
				}
			if (rs != null)
				try {
					rs.close();
				} catch (SQLException e) {
					logger.error(e.getMessage() + sql, e);
				}
		}
	}

	public List getResultList() {
		return getResultList(mapResultSetExtractor);
	}

	public Object getSingleResult() {
		return getSingleResult(mapResultSetExtractor);
	}

	public Query setFirstResult(int firstResult) {
		this.firstResult = firstResult;
		return this;
	}

	public Query setHint(String s, Object obj) {
		// TODO Query setHint(String s, Object obj)
		return null;
	}

	public Query setMaxResults(int maxResults) {
		this.maxResults = maxResults;
		return this;
	}

	public Query setParameter(String s, Object obj) {
		paramsMap.put(s, obj);
		return null;
	}

	public Query setParameter(int i, Object obj) {
		Parameter p = new Parameter();
		p.position = i;
		p.value = obj;
		this.params.add(p);
		return this;
	}

	public String getSql() {
		return sql;
	}

	public void setSql(String sql) {
		this.sql = sql;
		recordLstSql(sql);
	}

	public Session getSession() {
		return session;
	}

	public void setSession(Session session) {
		this.session = session;
	}

	public Object getSingleColumnResult() {
		return getSingleResult(singleColumnResultSetExtractor);
	}

	public List getSingleColumnResultList() {
		return this.getResultList(singleColumnResultSetExtractor);
	}

	/**
	 * 今后会再扩展，比如日期之类的会format一下。
	 * 
	 * @param o
	 * @return
	 */
	private String toString(Object o) {
		if (o == null)
			return "null";
		return o.toString();
	}

	public List getGeneratedKeys() {
		return generatedKeys;
	}

	public Object getGeneratedKey() {
		if (generatedKeys == null || generatedKeys.size() == 0)
			return null;
		return generatedKeys.get(0);
	}

}
